<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use think\Db;

/**
 * 
 *
 * @icon fa fa-circle-o
 */
class Tide extends Backend
{
    
    /**
     * Tide模型对象
     * @var \app\common\model\Tide
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\common\model\Tide;

    }

    public function import()
    {
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
//        $filePath = ROOT_PATH . DS . 'public' . DS . '/uploads/20210708/c863a5136d113ced6c7ca8f27d965c02.xlsx';
//        $filePath = ROOT_PATH . DS . 'public' . DS . '/uploads/20210708/9851cc8942ee7ee64fd7ddb4e7cc27ff.xlsx';
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($filePath); //载入excel表格

        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数
        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5

        $lines = $highestRow - 2;
        if ($lines <= 0) {
            $this->error('Excel表格中没有数据');
        }
        $temp = [];
        $tideData = [];
        Db::startTrans();
        try {
            for ($row = 2; $row <= $highestRow; ++$row) {
                // 读取时间
                $cellVal = $worksheet->getCellByColumnAndRow('1', $row)->getValue();
                if(empty($cellVal)) {
                    continue;
                }
                $cellVal = date('Y-m-d H:i:s', strtotime($cellVal));
                $temp[$row]['day_time'] = $cellVal;
                for ($i = 0; $i <= 23; $i++) {
                    $temp[$row]['height_' . $i] = $worksheet->getCellByColumnAndRow(($i + 2), $row)->getValue();
                }
                // 获取数据
                $tideData[$row]['day_time'] = $cellVal;
                for ($j = 1; $j <=4; $j++) {
                    $tideData[$row]['time_' . $j] = $worksheet->getCellByColumnAndRow(($i + $j + $j), $row)->getValue();
                    $tideData[$row]['height_' . $j] = $worksheet->getCellByColumnAndRow(($i + $j + $j + 1), $row)->getValue();
                }
            }
            \app\common\model\Tide::getInstance()->insertAll($temp);
            \app\common\model\TideInfo::getInstance()->insertAll($tideData);
            Db::commit();
            $this->success('导入成功');
        } catch (ValidateException $e) {
            Db::rollback();
            $this->error($e->getMessage());
        } catch (PDOException $e) {
            Db::rollback();
            $this->error($e->getMessage());
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */
    

}
